package com.sifei.seed.model;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

@Component
public class AppMusicSingerDao
{
    @Autowired // 通过Spring框架自动注入对象
    private JdbcTemplate jdbcTemplate;

    public List<AppMusicSinger> getSingerList(Integer pageSize, Integer pageNum) {
        final String sql = "select tb_song.singerid,tb_song.songName,tb_song.songid,imgUrl,singerName,profile from tb_artist left join tb_song on tb_artist.singerid=tb_song.singerid";
        List<AppMusicSinger> musicSingerList = jdbcTemplate.query(sql, new AppMusicSingerMapper());
        return musicSingerList;
    }

    public AppMusicSinger getSingerBysingerid(final Integer singerid) {
        final String sql = "select tb_song.singerid,tb_song.songName,tb_song.songid,imgUrl,singerName,profile from tb_artist left join tb_song on tb_artist.singerid=tb_song.singerid where tb_artist.singerid=" + singerid+ "";
        List<AppMusicSinger> musicConmunitiesList = jdbcTemplate.query(sql, new AppMusicSingerMapper());
        if (musicConmunitiesList == null || musicConmunitiesList.isEmpty()) {
            return null;
        }
        return musicConmunitiesList.get(0);
    }

    public AppMusicSinger getSingerBysingerName(final Long singerName) {
        final String sql = "select tb_song.singerid,tb_song.songName,tb_song.songid,imgUrl,singerName,profile from tb_artist left join tb_song on tb_artist.singerid=tb_song.singerid where singerName='" + singerName + "' limit 1";
        List<AppMusicSinger> musicConmunitiesList = jdbcTemplate.query(sql, new AppMusicSingerMapper());
        if (musicConmunitiesList == null || musicConmunitiesList.isEmpty()) {
            return null;
        }
        return musicConmunitiesList.get(0);
    }
    class AppMusicSingerMapper implements RowMapper<AppMusicSinger> {
        @Override
        public AppMusicSinger mapRow(ResultSet rs, int rowNum) throws SQLException {
            AppMusicSinger musicSinger = new AppMusicSinger();
            musicSinger.setSongid(rs.getInt("songid"));
            musicSinger.setSingerid(rs.getInt("singerid"));
            musicSinger.setSingerName(rs.getString("singerName"));
            musicSinger.setImgUrl(rs.getString("imgUrl"));
            musicSinger.setProfile(rs.getString("profile"));
            musicSinger.setSongName(rs.getString("songName"));
            return musicSinger;
        }
    }
}